package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import Bean.ZipcodeBean;

public class ZipcodeDao {
	private String driver = "oracle.jdbc.driver.OracleDriver"; // DB 접속에 사용할 드라이버
	private String url = "jdbc:oracle:thin:@localhost:1521:xe"; // 데이터베이스 출처
	private String user = "dustid"; // 사용자 아이디
	private String password = "dustpw"; // 사용자 비밀번호

	private Connection conn = null;

	public ZipcodeDao() {
		try {
			Class.forName(driver);
			conn = getConnection();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.out.println("해당드라이브가 존재하지 않습니다");
		}
	}
	
	public ArrayList<ZipcodeBean> getZipcodeList(String area3) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from zipcode where area3 like '" + area3 + "%' " ;
		ArrayList<ZipcodeBean> lists = null;
		try {
			lists = new ArrayList<ZipcodeBean>();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				ZipcodeBean zipcode = new ZipcodeBean();
				zipcode.setZipcode(rs.getString("zipcode"));
				zipcode.setArea1(rs.getString("area1"));
				zipcode.setArea2(rs.getString("area2"));
				zipcode.setArea3(rs.getString("area3"));
				zipcode.setArea4(rs.getString("area4"));
				lists.add(zipcode);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {	rs.close();	}
				if (pstmt != null) {	pstmt.close();	}
				closeConnection();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
		return lists;
	}

	private Connection getConnection() {
		try {
			return DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("커넥션 생성에 실패했습니다.");
			return null;
		}
	}
	private void closeConnection() {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
